using System;
using System.Collections.Generic;
using System.Text;
using Emlyn.Common.Data.SqlServer.SqlManagers;
using System.Data.SqlClient;
using Emlyn.Common.Tools.LayerInterfaces;

namespace Emlyn.Common.Data.SqlServer.DataLayer
{
    public abstract class SimpleTableDataLayer<T, TKey>: IBaseDataLayer<T, TKey>
    {
        protected abstract string TableName();
        protected abstract string KeyName();
        protected abstract IList<String> NonKeyAttributeNamesWritable();
        protected abstract IList<String> NonKeyAttributeNamesReadonly();
        protected abstract TKey DoGetKey(T aInstance);
        protected abstract object GetParameterValue(T aInstance, string aParameterName);

        public System.Data.DataSet Select(TKey aKey)
        {
            string lSql = "select * from " + TableName() + " where " + KeyName() + "=@" + KeyName();

            return SqlManager.GetDataSet(lSql, GetKeyParameters(aKey));
        }

        public System.Data.DataSet SelectAll()
        {
            string lSql = "select * from " + TableName();

            return SqlManager.GetDataSet(lSql, null);
        }

        public void Insert(T aInstance)
        {
            string lSql =
                "insert " + TableName() +
                " ([" + KeyName() + "]";

            foreach(string lNonKeyAttributeName in NonKeyAttributeNamesWritable())
            {
                lSql += ", [" + lNonKeyAttributeName + "]";
            }

            lSql += ") values (@" + KeyName();

            foreach(string lNonKeyAttributeName in NonKeyAttributeNamesWritable())
            {
                lSql += ", @" + lNonKeyAttributeName;
            }
                
            lSql += ")";
                
            SqlManager.Execute(lSql, GetWritableParameters(aInstance));
        }

        public void Update(T aInstance)
        {
            string lSql =
                "update " + TableName() + " set ";

            bool lFirstOne = true;
            foreach (string lNonKeyAttributeName in NonKeyAttributeNamesWritable())
            {
                if (!lFirstOne)
                    lSql += ", ";
                else
                    lFirstOne = false;

                lSql += "[" + lNonKeyAttributeName + "]=@" + lNonKeyAttributeName;
            }

            lSql += " where [" + KeyName() + "]=@" + KeyName();

            SqlManager.Execute(lSql, GetWritableParameters(aInstance));
        }

        public void Delete(TKey aKey)
        {
            string lSql =
                "delete " + TableName() + " where [" + KeyName() + "]=@" + KeyName();

            SqlManager.Execute(lSql, GetKeyParameters(aKey));
        }

        public List<System.Data.SqlClient.SqlParameter> GetFullParameters(T aInstance)
        {
            List<SqlParameter> lParameters = GetKeyParameters(GetKey(aInstance));

            foreach (string lNonKeyAttributeName in NonKeyAttributeNamesWritable())
            {
                SqlParameter lParameter = 
                    new SqlParameter(
                        "@" + lNonKeyAttributeName, 
                        GetParameterValue(aInstance, lNonKeyAttributeName)
                        );
                lParameters.Add(lParameter);
            }

            foreach (string lNonKeyAttributeName in NonKeyAttributeNamesReadonly())
            {
                SqlParameter lParameter =
                    new SqlParameter(
                        "@" + lNonKeyAttributeName,
                        GetParameterValue(aInstance, lNonKeyAttributeName)
                        );
                lParameters.Add(lParameter);
            }

            return lParameters;
        }

        public List<System.Data.SqlClient.SqlParameter> GetWritableParameters(T aInstance)
        {
            List<SqlParameter> lParameters = GetKeyParameters(GetKey(aInstance));

            foreach (string lNonKeyAttributeName in NonKeyAttributeNamesWritable())
            {
                SqlParameter lParameter =
                    new SqlParameter(
                        "@" + lNonKeyAttributeName,
                        GetParameterValue(aInstance, lNonKeyAttributeName)
                        );
                lParameters.Add(lParameter);
            }

            return lParameters;
        }

        public List<System.Data.SqlClient.SqlParameter> GetKeyParameters(TKey aKey)
        {
            List<SqlParameter> lParameters = new List<SqlParameter>();

            SqlParameter lParameter = new SqlParameter("@" + KeyName(), aKey);

            lParameters.Add(lParameter);

            return lParameters;
        }

        #region IBaseDataLayer<T,TKey> Members

        public TKey GetKey(T aInstance)
        {
            return DoGetKey(aInstance);
        }

        #endregion
    }
}
